from typing import List, Dict, Any, Optional, Union
from dataclasses import dataclass
import sys
import os
import json
from datetime import datetime

# 导入之前的模块
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
from database_connection import DatabaseManager, DatabaseConfig, DatabaseType
from table_manager import TableManager, ColumnInfo


@dataclass
class QueryCondition:
    """查询条件数据类"""
    column: str
    operator: str  # =, !=, >, <, >=, <=, LIKE, IN, NOT IN
    value: Any
    logical_operator: str = "AND"  # AND, OR


@dataclass
class QueryOptions:
    """查询选项数据类"""
    conditions: List[QueryCondition] = None
    order_by: str = None
    order_direction: str = "ASC"  # ASC, DESC
    limit: Optional[int] = None
    offset: Optional[int] = None


class DataManager:
    """数据管理器 - 处理CRUD操作"""

    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager
        self.table_manager = TableManager(db_manager)

    def insert(self, table_name: str, data: Dict[str, Any]) -> bool:
        """插入单条记录"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            # 获取表结构信息进行验证
            table_info = self.table_manager.get_table_info(table_name)
            if not table_info:
                raise Exception(f"表 '{table_name}' 不存在")

            # 验证数据
            validated_data = self._validate_data(table_info, data)

            # 构建INSERT语句
            columns = list(validated_data.keys())
            placeholders = ["?" if self.db_manager.config.db_type == DatabaseType.SQLITE else "%s"] * len(columns)

            insert_sql = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(placeholders)})"
            values = tuple(validated_data.values())

            affected_rows = self.db_manager.execute_non_query(insert_sql, values)

            if affected_rows > 0:
                print(f"成功插入 {affected_rows} 条记录到表 '{table_name}'")
                return True
            else:
                print("插入失败")
                return False

        except Exception as e:
            print(f"插入数据失败: {str(e)}")
            return False

    def insert_batch(self, table_name: str, data_list: List[Dict[str, Any]]) -> int:
        """批量插入记录"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not data_list:
            return 0

        try:
            # 获取表结构信息
            table_info = self.table_manager.get_table_info(table_name)
            if not table_info:
                raise Exception(f"表 '{table_name}' 不存在")

            success_count = 0

            for data in data_list:
                try:
                    if self.insert(table_name, data):
                        success_count += 1
                except Exception as e:
                    print(f"批量插入中的记录失败: {str(e)}")
                    continue

            print(f"批量插入完成，成功插入 {success_count}/{len(data_list)} 条记录")
            return success_count

        except Exception as e:
            print(f"批量插入失败: {str(e)}")
            return 0

    def select(self, table_name: str, columns: List[str] = None, options: QueryOptions = None) -> List[Dict[str, Any]]:
        """查询数据"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            # 构建SELECT语句
            select_columns = ", ".join(columns) if columns else "*"
            query = f"SELECT {select_columns} FROM {table_name}"
            params = []

            # 添加WHERE条件
            if options and options.conditions:
                where_clause, where_params = self._build_where_clause(options.conditions)
                query += f" WHERE {where_clause}"
                params.extend(where_params)

            # 添加ORDER BY
            if options and options.order_by:
                query += f" ORDER BY {options.order_by} {options.order_direction}"

            # 添加LIMIT和OFFSET
            if options and options.limit:
                if self.db_manager.config.db_type == DatabaseType.SQLITE:
                    query += f" LIMIT {options.limit}"
                    if options.offset:
                        query += f" OFFSET {options.offset}"
                elif self.db_manager.config.db_type == DatabaseType.MYSQL:
                    if options.offset:
                        query += f" LIMIT {options.offset}, {options.limit}"
                    else:
                        query += f" LIMIT {options.limit}"
                elif self.db_manager.config.db_type == DatabaseType.POSTGRESQL:
                    query += f" LIMIT {options.limit}"
                    if options.offset:
                        query += f" OFFSET {options.offset}"

            # 执行查询
            results = self.db_manager.execute_query(query, tuple(params) if params else None)
            print(f"查询到 {len(results)} 条记录")
            return results

        except Exception as e:
            print(f"查询数据失败: {str(e)}")
            return []

    def update(self, table_name: str, data: Dict[str, Any], conditions: List[QueryCondition]) -> int:
        """更新数据"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not conditions:
            raise Exception("更新操作必须指定条件，以防止误操作")

        try:
            # 获取表结构信息进行验证
            table_info = self.table_manager.get_table_info(table_name)
            if not table_info:
                raise Exception(f"表 '{table_name}' 不存在")

            # 验证数据
            validated_data = self._validate_data(table_info, data, is_update=True)

            # 构建UPDATE语句
            placeholder = "?" if self.db_manager.config.db_type == DatabaseType.SQLITE else "%s"
            set_clauses = [f"{col} = {placeholder}" for col in validated_data.keys()]
            set_clause = ", ".join(set_clauses)

            where_clause, where_params = self._build_where_clause(conditions)

            update_sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"
            params = list(validated_data.values()) + where_params

            affected_rows = self.db_manager.execute_non_query(update_sql, tuple(params))

            print(f"成功更新 {affected_rows} 条记录")
            return affected_rows

        except Exception as e:
            print(f"更新数据失败: {str(e)}")
            return 0

    def delete(self, table_name: str, conditions: List[QueryCondition]) -> int:
        """删除数据"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        if not conditions:
            raise Exception("删除操作必须指定条件，以防止误操作")

        try:
            # 构建DELETE语句
            where_clause, where_params = self._build_where_clause(conditions)
            delete_sql = f"DELETE FROM {table_name} WHERE {where_clause}"

            affected_rows = self.db_manager.execute_non_query(delete_sql, tuple(where_params))

            print(f"成功删除 {affected_rows} 条记录")
            return affected_rows

        except Exception as e:
            print(f"删除数据失败: {str(e)}")
            return 0

    def count(self, table_name: str, conditions: List[QueryCondition] = None) -> int:
        """统计记录数"""
        if not self.db_manager.connection:
            raise Exception("数据库未连接")

        try:
            query = f"SELECT COUNT(*) as count FROM {table_name}"
            params = []

            if conditions:
                where_clause, where_params = self._build_where_clause(conditions)
                query += f" WHERE {where_clause}"
                params.extend(where_params)

            result = self.db_manager.execute_query(query, tuple(params) if params else None)
            return result[0]['count'] if result else 0

        except Exception as e:
            print(f"统计记录数失败: {str(e)}")
            return 0

    def exists(self, table_name: str, conditions: List[QueryCondition]) -> bool:
        """检查记录是否存在"""
        return self.count(table_name, conditions) > 0

    def _validate_data(self, table_info, data: Dict[str, Any], is_update: bool = False) -> Dict[str, Any]:
        """验证数据"""
        validated_data = {}

        # 获取表的列信息
        column_map = {col.name: col for col in table_info.columns}

        for key, value in data.items():
            if key not in column_map:
                print(f"警告: 列 '{key}' 在表中不存在，已忽略")
                continue

            column = column_map[key]

            # 检查非空约束
            if not column.is_nullable and value is None and not is_update:
                raise Exception(f"列 '{key}' 不能为空")

            # 类型转换和验证
            validated_value = self._convert_value(value, column.data_type)
            validated_data[key] = validated_value

        return validated_data

    def _convert_value(self, value: Any, data_type: str) -> Any:
        """转换和验证数据类型"""
        if value is None:
            return None

        data_type_upper = data_type.upper()

        try:
            if "INT" in data_type_upper:
                return int(value)
            elif "REAL" in data_type_upper or "FLOAT" in data_type_upper or "DOUBLE" in data_type_upper:
                return float(value)
            elif "BOOL" in data_type_upper:
                if isinstance(value, str):
                    return value.lower() in ('true', '1', 'yes', 'on')
                return bool(value)
            elif "DATE" in data_type_upper or "TIME" in data_type_upper:
                if isinstance(value, str):
                    return value  # 假设字符串格式正确
                elif isinstance(value, datetime):
                    return value.isoformat()
                return str(value)
            else:
                # TEXT, VARCHAR, CHAR等字符串类型
                return str(value)

        except (ValueError, TypeError) as e:
            raise Exception(f"数据类型转换失败: {value} -> {data_type}, 错误: {str(e)}")

    def _build_where_clause(self, conditions: List[QueryCondition]) -> tuple:
        """构建WHERE子句"""
        if not conditions:
            return "", []

        where_parts = []
        params = []
        placeholder = "?" if self.db_manager.config.db_type == DatabaseType.SQLITE else "%s"

        for i, condition in enumerate(conditions):
            if i > 0:
                where_parts.append(condition.logical_operator)

            if condition.operator.upper() == "IN":
                if isinstance(condition.value, (list, tuple)):
                    placeholders = ", ".join([placeholder] * len(condition.value))
                    where_parts.append(f"{condition.column} IN ({placeholders})")
                    params.extend(condition.value)
                else:
                    raise Exception("IN操作符的值必须是列表或元组")
            elif condition.operator.upper() == "NOT IN":
                if isinstance(condition.value, (list, tuple)):
                    placeholders = ", ".join([placeholder] * len(condition.value))
                    where_parts.append(f"{condition.column} NOT IN ({placeholders})")
                    params.extend(condition.value)
                else:
                    raise Exception("NOT IN操作符的值必须是列表或元组")
            elif condition.operator.upper() == "LIKE":
                where_parts.append(f"{condition.column} LIKE {placeholder}")
                params.append(condition.value)
            else:
                where_parts.append(f"{condition.column} {condition.operator} {placeholder}")
                params.append(condition.value)

        return " ".join(where_parts), params

    def print_query_results(self, results: List[Dict[str, Any]], title: str = "查询结果"):
        """格式化打印查询结果"""
        if not results:
            print(f"{title}: 无数据")
            return

        print(f"\n{title} ({len(results)} 条记录):")
        print("-" * 80)

        # 获取所有列名
        columns = list(results[0].keys())

        # 打印表头
        header = " | ".join([f"{col:<15}" for col in columns])
        print(header)
        print("-" * len(header))

        # 打印数据行
        for row in results:
            row_data = " | ".join([f"{str(row.get(col, '')):<15}" for col in columns])
            print(row_data)


# 演示和测试代码
if __name__ == "__main__":
    print("数据库管理工具 - 数据CRUD操作模块测试")
    print("=" * 50)

    # 创建数据库连接
    from database_connection import DatabaseConfig, DatabaseType

    db_manager = DatabaseManager()
    config = DatabaseConfig(
        db_type=DatabaseType.SQLITE,
        database="test_database.db"
    )

    if db_manager.connect(config):
        data_manager = DataManager(db_manager)

        # 确保测试表存在
        print("\n准备测试表...")
        table_manager = TableManager(db_manager)

        # 创建测试表（如果不存在）
        columns = [
            ColumnInfo(name="id", data_type="INTEGER", is_primary_key=True, is_auto_increment=True, is_nullable=False),
            ColumnInfo(name="name", data_type="VARCHAR", max_length=100, is_nullable=False),
            ColumnInfo(name="email", data_type="VARCHAR", max_length=255, is_unique=True, is_nullable=False),
            ColumnInfo(name="age", data_type="INTEGER", default_value="0"),
            ColumnInfo(name="department", data_type="VARCHAR", max_length=50, default_value="'未分配'"),
            ColumnInfo(name="salary", data_type="REAL"),
            ColumnInfo(name="created_at", data_type="DATETIME", default_value="CURRENT_TIMESTAMP")
        ]

        # 删除旧表并重新创建
        table_manager.drop_table("test_employees")
        table_manager.create_table("test_employees", columns)

        # 测试插入数据
        print("\n测试插入数据...")
        test_data = [
            {"name": "张三", "email": "zhangsan@test.com", "age": 25, "department": "开发部", "salary": 8000.0},
            {"name": "李四", "email": "lisi@test.com", "age": 30, "department": "测试部", "salary": 7000.0},
            {"name": "王五", "email": "wangwu@test.com", "age": 28, "department": "开发部", "salary": 9000.0},
            {"name": "赵六", "email": "zhaoliu@test.com", "age": 35, "department": "产品部", "salary": 10000.0},
            {"name": "钱七", "email": "qianqi@test.com", "age": 26, "department": "设计部", "salary": 7500.0}
        ]

        # 批量插入
        data_manager.insert_batch("test_employees", test_data)

        # 测试查询数据
        print("\n测试查询所有数据...")
        all_results = data_manager.select("test_employees")
        data_manager.print_query_results(all_results, "所有员工")

        # 测试条件查询
        print("\n测试条件查询 - 开发部员工...")
        conditions = [
            QueryCondition(column="department", operator="=", value="开发部")
        ]
        dev_results = data_manager.select("test_employees", options=QueryOptions(conditions=conditions))
        data_manager.print_query_results(dev_results, "开发部员工")

        # 测试复合条件查询
        print("\n测试复合条件查询 - 年龄大于27且薪资大于7500的员工...")
        conditions = [
            QueryCondition(column="age", operator=">", value=27),
            QueryCondition(column="salary", operator=">", value=7500.0, logical_operator="AND")
        ]
        complex_results = data_manager.select("test_employees", options=QueryOptions(conditions=conditions))
        data_manager.print_query_results(complex_results, "符合条件的员工")

        # 测试排序和分页
        print("\n测试排序查询 - 按薪资降序...")
        sorted_results = data_manager.select(
            "test_employees",
            options=QueryOptions(order_by="salary", order_direction="DESC", limit=3)
        )
        data_manager.print_query_results(sorted_results, "薪资前3名")

        # 测试更新数据
        print("\n测试更新数据 - 给开发部员工涨薪1000...")
        update_conditions = [
            QueryCondition(column="department", operator="=", value="开发部")
        ]
        update_data = {"salary": 9000.0}  # 这里简化处理，实际应该用SQL表达式

        # 先查询开发部员工当前薪资
        dev_employees = data_manager.select("test_employees", options=QueryOptions(conditions=update_conditions))
        for emp in dev_employees:
            new_salary = emp['salary'] + 1000
            emp_conditions = [QueryCondition(column="id", operator="=", value=emp['id'])]
            data_manager.update("test_employees", {"salary": new_salary}, emp_conditions)

        # 查看更新后的结果
        updated_results = data_manager.select("test_employees", options=QueryOptions(conditions=update_conditions))
        data_manager.print_query_results(updated_results, "涨薪后的开发部员工")

        # 测试统计功能
        print("\n测试统计功能...")
        total_count = data_manager.count("test_employees")
        dev_count = data_manager.count("test_employees",
                                       [QueryCondition(column="department", operator="=", value="开发部")])
        print(f"总员工数: {total_count}")
        print(f"开发部员工数: {dev_count}")

        # 测试存在性检查
        exists_check = data_manager.exists("test_employees",
                                           [QueryCondition(column="email", operator="=", value="zhangsan@test.com")])
        print(f"邮箱 'zhangsan@test.com' 是否存在: {exists_check}")

        # 测试删除数据
        print("\n测试删除数据 - 删除年龄小于27的员工...")
        delete_conditions = [
            QueryCondition(column="age", operator="<", value=27)
        ]
        deleted_count = data_manager.delete("test_employees", delete_conditions)

        # 查看删除后的结果
        final_results = data_manager.select("test_employees")
        data_manager.print_query_results(final_results, "删除后的员工列表")

        db_manager.disconnect()

    print("\n数据CRUD操作模块测试完成！")
